package com.etone.smartAudit.utils.word;

import com.etone.daemon.db.helper.QueryHelper;
import com.etone.smartAudit.utils.DateUtil;
import com.etone.smartAudit.utils.NumberUtil;
import com.etone.smartAudit.utils.ObjectUtil;
import com.etone.smartAudit.vo.auditExceptionCityReport.AuditExceptionCityReportSpecialVO;
import com.etone.smartAudit.vo.auditExceptionCityReport.AuditExceptionCityReportTypeVO;
import lombok.extern.log4j.Log4j2;
import org.apache.poi.xwpf.usermodel.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author: sovy
 * @date: 2021/1/21
 * @description:
 */
@Log4j2
public class AuditExceptionCityReportWordUtil {

    private XWPFDocument xwpfDocument;

    private Map<String, Object> datas;


    private final int indentation = 350;

    private Connection connection = null;

    private String dx[] = {"一", "二", "三", "四", "五"};


    public AuditExceptionCityReportWordUtil(XWPFDocument xwpfDocument, Map<String, Object> datas, Connection connection) {
        this.xwpfDocument = xwpfDocument;
        this.datas = datas;
        this.connection = connection;
    }

    public XWPFDocument run() {
        log.info("--------------------------------------------------------------");
        List<String> paragraphTargets = getRemoveParagraphTarget(xwpfDocument, datas);
        remove(xwpfDocument, paragraphTargets);
        getParams(datas);
        replaceParams(xwpfDocument, datas);
        return xwpfDocument;
    }


    public void createSpecialContent() {
        List<AuditExceptionCityReportSpecialVO> specials = (List<AuditExceptionCityReportSpecialVO>) datas.get("specials");
        int specialIndex = 0;
        for (AuditExceptionCityReportSpecialVO special : specials) {
            createSpecial(dx[specialIndex++] + "、" + special.getName());
            int typeIndex = 1;
            for (AuditExceptionCityReportTypeVO type : special.getTypes()) {
                createType((typeIndex++) + "、" + type.getName());
                createTypeContent(type.getParams().stream().map(param -> param.getValue()).collect(Collectors.joining("")));
            }

        }

    }

    /**
     * 创建专题
     *
     * @param text
     */
    public void createSpecial(String text) {
        xwpfDocument.createParagraph();
        XWPFParagraph paragraph = xwpfDocument.createParagraph();
        XWPFRun run = paragraph.createRun();
        run.setFontFamily("黑体");
        run.setFontSize(16);
        run.setText(text);
    }

    /**
     * 创建审计点
     *
     * @param text
     */
    public void createType(String text) {
        XWPFParagraph paragraph = xwpfDocument.createParagraph();

        XWPFRun run = paragraph.createRun();
        run.setFontFamily("黑体");
        run.setFontSize(15);
        run.setText(text);
        //首行缩进
        paragraph.setIndentationFirstLine(indentation);
    }

    /**
     * 创建审计点内容
     *
     * @param text
     */
    public void createTypeContent(String text) {
        XWPFParagraph paragraph = xwpfDocument.createParagraph();
        //首行缩进
        paragraph.setIndentationFirstLine(indentation);
        XWPFRun run = paragraph.createRun();
        run.setFontFamily("仿宋");
        run.setFontSize(16);
        run.setText(text);
    }

    /**
     * 创建标题
     */
    public void createTile() {
        XWPFParagraph paragraph = xwpfDocument.createParagraph();
        XWPFRun run = paragraph.createRun();
        run.setText("审计报告");
        run.setBold(true);
        run.setFontFamily("宋体");
        run.setFontSize(24);
        paragraph.setAlignment(ParagraphAlignment.CENTER);
    }

    /**
     * 创建总体说明
     */
    public void createGeneralDescription() {
        XWPFParagraph paragraph = xwpfDocument.createParagraph();

        String text = datas.get("date") + "对" + datas.get("city") + "分公司“客户欠费”、“营销管理”、“合同管理”、“酬金管理”和“家宽终端”五个模块共计" + datas.get("typeCnt") + "个控制点开展持续审计，审计发现如下：";
        XWPFRun run = paragraph.createRun();
        run.setText(text);
        run.setFontFamily("仿宋");
        run.setFontSize(16);
        //首行缩进
        paragraph.setIndentationFirstLine(indentation);
    }

    /**
     * 创建底部
     */
    public void createFooter() {
        xwpfDocument.createParagraph();
        XWPFParagraph paragraph = xwpfDocument.createParagraph();
        String text = "编制部门：省公司内审部   编制日期：" + datas.get("today");
        XWPFRun run = paragraph.createRun();
        run.setText(text);
        run.setFontFamily("仿宋");
        run.setFontSize(16);
    }

    public void replaceParams(XWPFDocument xwpfDocument, Map<String, Object> dataMap) {
        List<XWPFParagraph> paragraphs = xwpfDocument.getParagraphs();
        List<XWPFTable> tables = xwpfDocument.getTables();

        String regex = "\\$\\{(.*?)}";
        Pattern pattern = Pattern.compile(regex);

        log.info("------------------paragraph------------------");
        //替换段落内容
        for (XWPFParagraph paragraph : paragraphs) {
            List<XWPFRun> runs = paragraph.getRuns();
            for (XWPFRun run : runs) {
                String text = run.text();
                run.setText(replaceText(pattern, text, dataMap), 0);
            }
        }
        log.info("------------------table------------------");
        for (XWPFTable table : tables) {
            List<XWPFTableRow> rows = table.getRows();
            for (XWPFTableRow row : rows) {
                List<XWPFTableCell> tableCells = row.getTableCells();
                for (XWPFTableCell tableCell : tableCells) {
                    List<XWPFParagraph> cellParagraphs = tableCell.getParagraphs();
                    for (XWPFParagraph cellParagraph : cellParagraphs) {
                        List<XWPFRun> runs = cellParagraph.getRuns();
                        for (XWPFRun run : runs) {
                            String text = run.text();
                            run.setText(replaceText(pattern, text, dataMap), 0);
                        }
                    }
                }
            }
        }

    }

    public String replaceText(Pattern pattern, String text, Map<String, Object> dataMap) {
        Matcher matcher = pattern.matcher(text);
        while (matcher.find()) {
            String group = matcher.group(1);
            if (dataMap.containsKey(group)) {
                String replaceValue = String.valueOf(dataMap.get(group));
                text = text.replaceAll("\\$\\{" + group + "\\}", replaceValue);
            } else {
                continue;
            }
            matcher.reset(text);
        }

        return text;
    }

    /**
     * 删除多个段落
     *
     * @param xwpfDocument
     * @param keys
     */
    public void remove(XWPFDocument xwpfDocument, List<String> keys) {

        //删除段落
        for (String key : keys) {
            remove(xwpfDocument, key);
        }

        //删除标签
        List<XWPFParagraph> removeElements = new ArrayList<>();
        String regex = "^<!--(.+)-->$";
        Pattern pattern = Pattern.compile(regex);
        for (XWPFParagraph paragraph : xwpfDocument.getParagraphs()) {
            String text = paragraph.getText();
            Matcher matcher = pattern.matcher(text);
            if (matcher.find()) {
                removeElements.add(paragraph);
            }
        }
        for (XWPFParagraph removeElement : removeElements) {
            xwpfDocument.removeBodyElement(xwpfDocument.getPosOfParagraph(removeElement));
        }
    }

    /**
     * 删除段落
     *
     * @param xwpfDocument
     * @param key
     */
    public void remove(XWPFDocument xwpfDocument, String key) {

        List<IBodyElement> removeElements = new ArrayList<>();
        List<IBodyElement> bodyElements = xwpfDocument.getBodyElements();
        boolean isDelete = false;
        for (IBodyElement bodyElement : bodyElements) {
            switch (bodyElement.getElementType()) {
                case PARAGRAPH:
                    XWPFParagraph xwpfParagraph = (XWPFParagraph) bodyElement;
                    String text = xwpfParagraph.getText();
                    if (text.contains("<!--" + key + "-->")) {
                        isDelete = !isDelete;
                    }
                    break;
            }
            if (isDelete) {
                removeElements.add(bodyElement);
            }
        }

        removeElements = removeElements.stream().distinct().collect(Collectors.toList());
        for (IBodyElement bodyElement : removeElements) {
            switch (bodyElement.getElementType()) {
                case PARAGRAPH:
                    XWPFParagraph xwpfParagraph = (XWPFParagraph) bodyElement;
                    xwpfDocument.removeBodyElement(xwpfDocument.getPosOfParagraph(xwpfParagraph));
                    break;
                case TABLE:
                    XWPFTable table = (XWPFTable) bodyElement;
                    xwpfDocument.removeBodyElement(xwpfDocument.getPosOfTable(table));
                    break;
            }

        }

    }

    /**
     * 获取要被删除的段落标签
     *
     * @param datas
     * @param xwpfDocument
     * @return
     */
    public List<String> getRemoveParagraphTarget(XWPFDocument xwpfDocument, Map<String, Object> datas) {
        List<String> paragraphTargets = getParagraphTarget(xwpfDocument);
        for (String key : datas.keySet()) {
            if (paragraphTargets.contains(key)) {
                paragraphTargets.remove(key);
            }
        }
        return paragraphTargets;
    }

    /**
     * 获取段落标签
     *
     * @return
     */
    public List<String> getParagraphTarget(XWPFDocument xwpfDocument) {
        List<String> targets = new ArrayList<>();
        List<XWPFParagraph> paragraphs = xwpfDocument.getParagraphs();
        String regex = "^<!--(.+)-->$";
        Pattern pattern = Pattern.compile(regex);
        for (XWPFParagraph paragraph : paragraphs) {
            String text = paragraph.getText();
            Matcher matcher = pattern.matcher(text);
            if (matcher.find()) {
                String group = matcher.group(1);
                if (!targets.contains(group)) {
                    targets.add(group);
                }

            }
        }
        return targets;
    }

    /**
     * 获取参数
     *
     * @param params
     */
    public void getParams(Map<String, Object> params) {
        int exceptionCnt = 0;
        try {
            if (params.containsKey("z_1")) {
                if (params.containsKey("z_1_1")) {
                    p_1_1(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_1_2")) {
                    p_1_2(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_1_3")) {
                    p_1_3(connection, params);
                    exceptionCnt++;
                }
            }


            if (params.containsKey("z_2")) {
                if (params.containsKey("z_2_1")) {
                    p_2_1(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_2_2")) {
                    p_2_2(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_2_3")) {
                    p_2_3(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_2_4")) {
                    p_2_4(connection, params);
                    exceptionCnt++;
                }
            }


            if (params.containsKey("z_3")) {
                if (params.containsKey("z_3_1")) {
                    p_3_1(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_3_2")) {
                    p_3_2(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_3_3")) {
                    p_3_3(connection, params);
                    exceptionCnt++;
                }
            }

            if (params.containsKey("z_4")) {
                if (params.containsKey("z_4_1")) {
                    p_4_1(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_4_2")) {
                    p_4_2(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_4_3")) {
                    p_4_3(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_4_4")) {
                    p_4_4(connection, params);
                    exceptionCnt++;
                }
            }

            if (params.containsKey("z_5")) {
                if (params.containsKey("z_5_1")) {
                    p_5_1(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_5_2")) {
                    p_5_2(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_5_3")) {
                    p_5_3(connection, params);
                    exceptionCnt++;
                }
                if (params.containsKey("z_5_4")) {
                    p_5_4(connection, params);
                    exceptionCnt++;
                }
            }
            datas.put("exceptionCnt", exceptionCnt);

        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }

    }

    /**
     * 一、客户欠费业务违规情况 --> 1、非红名单集团产品欠费超期未停机
     *
     * @param connection
     * @param params
     */
    public void p_1_1(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_1_1_";
        String gpSchema = String.valueOf(params.get("gpSchema"));
        //段落一
        String sql = "SELECT max(date) date FROM " + gpSchema + ".f_audit_arrearage WHERE report_periods = '" + params.get("reportPeriods") + "'";
        Object date = QueryHelper.selectColumn(connection, sql, "date");
        params.put(keyPrefix + "1_date", date == null ? null : DateUtil.turnJavaUtilDateToStrDate(DateUtil.parseAddDate(DateUtil.turnStrDateToJavaUtilDate(date.toString(), "yyyyMM"), Calendar.MONTH, -1), "yyyy年MM月"));
        params.put("date", date == null ? null : new StringBuilder(date.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT COUNT(DISTINCT product_code) c FROM " + gpSchema + ".f_audit_arrearage WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT SUM(beyond_money) s FROM " + gpSchema + ".f_audit_arrearage WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);
    }

    /**
     * 一、客户欠费业务违规情况 --> 2、集团产品欠费停机超期未销户
     *
     * @param connection
     * @param params
     */
    public void p_1_2(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_1_2_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) date FROM " + gpSchema + ".f_audit_blocarrear WHERE report_periods = '" + params.get("reportPeriods") + "'";
        Object date = QueryHelper.selectColumn(connection, sql, "date");
        params.put(keyPrefix + "1_date", date == null ? null : DateUtil.turnJavaUtilDateToStrDate(DateUtil.parseAddDate(DateUtil.turnStrDateToJavaUtilDate(date.toString(), "yyyyMM"), Calendar.MONTH, -1), "yyyy年MM月"));

        sql = "SELECT COUNT(DISTINCT product_code) c FROM " + gpSchema + ".f_audit_blocarrear WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT SUM(money) s FROM " + gpSchema + ".f_audit_blocarrear WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);
    }

    /**
     * 一、客户欠费业务违规情况 --> 3、欠费销户集团客户未进入黑名单
     *
     * @param connection
     * @param params
     */
    public void p_1_3(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_1_3_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) date FROM " + gpSchema + ".f_audit_blocarrear_blacklist WHERE report_periods = '" + params.get("reportPeriods") + "'";
        Object date = QueryHelper.selectColumn(connection, sql, "date");
        params.put(keyPrefix + "1_date", date == null ? null : new StringBuilder(date.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT COUNT(DISTINCT product_code) c FROM " + gpSchema + ".f_audit_blocarrear_blacklist WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT SUM(money) s FROM " + gpSchema + ".f_audit_blocarrear_blacklist WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);
    }

    /**
     * 二、营销管理业务违规情况 --> 1、单用户单月高额积分异常变动
     *
     * @param connection
     * @param params
     */
    public void p_2_1(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_2_1_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_integral_change WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "1_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT COUNT(DISTINCT msisdn) c FROM " + gpSchema + ".f_audit_integral_change WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);
    }

    /**
     * 二、营销管理业务违规情况 --> 2、通过调账方式违规为黑名单客户办理优惠方案
     *
     * @param connection
     * @param params
     */
    public void p_2_2(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_2_2_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_give_resource WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "1_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT SUM(change_money) s FROM " + gpSchema + ".f_audit_give_resource WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_1", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);
    }

    /**
     * 二、营销管理业务违规情况 --> 3、集团代付产品违规转售
     *
     * @param connection
     * @param params
     */
    public void p_2_3(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_2_3_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_pay_group WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "1_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT COUNT(DISTINCT group_code) c FROM " + gpSchema + ".f_audit_pay_group WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT SUM(pay_money) s FROM " + gpSchema + ".f_audit_pay_group WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);
    }

    /**
     * 二、营销管理业务违规情况 --> 4、承诺低消类营销案未互斥
     *
     * @param connection
     * @param params
     */
    public void p_2_4(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_2_4_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_low_sale_mutex WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "1_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT COUNT(DISTINCT product_code) c FROM " + gpSchema + ".f_audit_low_sale_mutex WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);
    }

    /**
     * 三、合同管理业务违规情况 --> 1、超五万元项目未签署合同
     *
     * @param connection
     * @param params
     */
    public void p_3_1(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_3_1_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_unsign_contract WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "1_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT COUNT(1) c FROM " + gpSchema + ".f_audit_unsign_contract WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT SUM(money) s FROM " + gpSchema + ".f_audit_unsign_contract WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);
    }

    /**
     * 三、合同管理业务违规情况 --> 2、合同拆分规避分级审批
     *
     * @param connection
     * @param params
     */
    public void p_3_2(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_3_2_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_level_audit WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "1_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT COUNT(1) c FROM " + gpSchema + ".f_audit_level_audit WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT SUM(ltail) s FROM " + gpSchema + ".f_audit_level_audit WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);
    }

    /**
     * 三、合同管理业务违规情况 --> 3、合同拆分规避采购流程
     *
     * @param connection
     * @param params
     */
    public void p_3_3(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_3_3_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_proc_process WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "1_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT COUNT(1) c FROM " + gpSchema + ".f_audit_proc_process WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT lop_name) c FROM " + gpSchema + ".f_audit_proc_process WHERE  " + commonWhere;
        count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", count);

        sql = "SELECT SUM(ltail) s FROM " + gpSchema + ".f_audit_proc_process WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);
    }

    /**
     * 四、酬金管理业务违规情况 --> 1、酬金异常支付
     *
     * @param connection
     * @param params
     */
    public void p_4_1(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_4_1_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_mony_exception_pay WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "1_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT SUM(money_total) s FROM " + gpSchema + ".f_audit_mony_exception_pay WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_1", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);
    }

    /**
     * 四、酬金管理业务违规情况 --> 2、酬金重复结算
     *
     * @param connection
     * @param params
     */
    public void p_4_2(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_4_2_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_mony_double_settlement WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "1_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT SUM(money) s FROM " + gpSchema + ".f_audit_mony_double_settlement WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_1", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);
    }

    /**
     * 四、酬金管理业务违规情况 --> 3、门店补贴超标准结算
     *
     * @param connection
     * @param params
     */
    public void p_4_3(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_4_3_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_shopsubsidy WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "1_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT SUM(sbvlm) s FROM " + gpSchema + ".f_audit_shopsubsidy WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_1", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);
    }

    /**
     * 四、酬金管理业务违规情况 --> 4、酬金调整异常操作
     *
     * @param connection
     * @param params
     */
    public void p_4_4(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_4_4_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_num_null WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "1_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT business_name FROM " + gpSchema + ".f_audit_num_null WHERE  " + commonWhere + " GROUP BY 1 order by sum(money) desc limit 2";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        String businessNames = maps.stream().map(map -> String.valueOf(map.get("business_name"))).collect(Collectors.joining(","));
        params.put(keyPrefix + "1_1", businessNames);

        sql = "SELECT SUM(money) s FROM " + gpSchema + ".f_audit_num_null WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", sum != null ? NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D) : null);

        //段落二，达标值异常调低
        sql = "SELECT max(date) endDate, min(date) startDate FROM " + gpSchema + ".f_audit_value_lower WHERE report_periods = '" + params.get("reportPeriods") + "'";
        rangeDate = QueryHelper.selectMap(connection, sql);
        startDate = rangeDate.get(0).get("startDate");
        endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "2_startDate", startDate == null ? null : new StringBuilder(startDate.toString()).insert(4, "年").append("月").toString());
        params.put(keyPrefix + "2_endDate", endDate == null ? null : new StringBuilder(endDate.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT count(1) tc FROM " + gpSchema + ".f_audit_value_lower WHERE  " + commonWhere;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "tc");
        params.put(keyPrefix + "2_1", totalCount);

        sql = "SELECT count(DISTINCT qrbsi) c FROM " + gpSchema + ".f_audit_value_lower WHERE  " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "2_2", count);
    }

    /**
     * 五、家宽终端管理业务违规情况 --> 1、装维人员领用设备长期未送装
     *
     * @param connection
     * @param params
     */
    public void p_5_1(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_5_1_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(use_time::timestamp) endDate, min(use_time::timestamp) startDate FROM " + gpSchema + ".f_audit_equipmentuninstalled WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : DateUtil.turnStrDateToStrDate(startDate.toString(), DateUtil.DF_yyyy_MM_dd_HH_mm_ss, DateUtil.DF_yyyyYMMM));
        params.put(keyPrefix + "1_endDate", endDate == null ? null : DateUtil.turnStrDateToStrDate(endDate.toString(), DateUtil.DF_yyyy_MM_dd_HH_mm_ss, DateUtil.DF_yyyyYMMM));

        sql = "SELECT COUNT(DISTINCT tsc) c FROM " + gpSchema + ".f_audit_equipmentuninstalled WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);
    }

    /**
     * 五、家宽终端管理业务违规情况 --> 2、已销户宽带长期未拆机
     *
     * @param connection
     * @param params
     */
    public void p_5_2(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_5_2_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(state_time::timestamp) endDate, min(state_time::timestamp) startDate FROM " + gpSchema + ".f_audit_unmachine_broadband WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : DateUtil.turnStrDateToStrDate(startDate.toString(), DateUtil.DF_yyyy_MM_dd_HH_mm_ss, DateUtil.DF_yyyyYMMM));
        params.put(keyPrefix + "1_endDate", endDate == null ? null : DateUtil.turnStrDateToStrDate(endDate.toString(), DateUtil.DF_yyyy_MM_dd_HH_mm_ss, DateUtil.DF_yyyyYMMM));

        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_unmachine_broadband WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);
    }

    /**
     * 五、家宽终端管理业务违规情况 --> 3、已销户或离网家庭用户终端未回收
     *
     * @param connection
     * @param params
     */
    public void p_5_3(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_5_3_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        //离网时间
        String sql = "SELECT max(state_time::timestamp) endDate, min(state_time::timestamp) startDate FROM " + gpSchema + ".f_audit_unrecovery_terminal WHERE report_periods = '" + params.get("reportPeriods") + "'";
        List<Map<String, Object>> rangeDate = QueryHelper.selectMap(connection, sql);
        Object startDate = rangeDate.get(0).get("startDate");
        Object endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_startDate", startDate == null ? null : DateUtil.turnStrDateToStrDate(startDate.toString(), DateUtil.DF_yyyy_MM_dd_HH_mm_ss, DateUtil.DF_yyyyYMMM));
        params.put(keyPrefix + "1_endDate", endDate == null ? null : DateUtil.turnStrDateToStrDate(endDate.toString(), DateUtil.DF_yyyy_MM_dd_HH_mm_ss, DateUtil.DF_yyyyYMMM));

        //开通时间
        sql = "SELECT max(open_time::timestamp) endDate, min(open_time::timestamp) startDate FROM " + gpSchema + ".f_audit_unrecovery_terminal WHERE report_periods = '" + params.get("reportPeriods") + "'";
        rangeDate = QueryHelper.selectMap(connection, sql);
        startDate = rangeDate.get(0).get("startDate");
        endDate = rangeDate.get(0).get("endDate");
        params.put(keyPrefix + "1_openStartDate", startDate == null ? null : DateUtil.turnStrDateToStrDate(startDate.toString(), DateUtil.DF_yyyy_MM_dd_HH_mm_ss, DateUtil.DF_yyyyYMMM));
        params.put(keyPrefix + "1_openEndDate", endDate == null ? null : DateUtil.turnStrDateToStrDate(endDate.toString(), DateUtil.DF_yyyy_MM_dd_HH_mm_ss, DateUtil.DF_yyyyYMMM));

        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_unrecovery_terminal WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);
    }

    /**
     * 五、家宽终端管理业务违规情况 --> 4、已报废终端仍在网使用
     *
     * @param connection
     * @param params
     */
    public void p_5_4(Connection connection, Map<String, Object> params) throws SQLException {
        String commonWhere = " city_name ='" + params.get("city") + "' AND report_periods = '" + params.get("reportPeriods") + "'";
        String keyPrefix = "p_5_4_";
        String gpSchema = String.valueOf(params.get("gpSchema"));

        //段落一
        String sql = "SELECT max(date) date FROM " + gpSchema + ".f_audit_terminal_broadband WHERE report_periods = '" + params.get("reportPeriods") + "'";
        Object date = QueryHelper.selectColumn(connection, sql, "date");
        params.put(keyPrefix + "1_date", date == null ? null : new StringBuilder(date.toString()).insert(4, "年").append("月").toString());

        sql = "SELECT COUNT(DISTINCT imei) c FROM " + gpSchema + ".f_audit_terminal_broadband WHERE " + commonWhere;
        Object onuCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", onuCount);
    }
}
